package sanrenxing.right.dao;


import sanrenxing.right.entity.RoleEntity;
import sanrenxing.right.entity.RoleTreeEntity;
import sanrenxing.right.entity.UserEntity;
import sanrenxing.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * 角色数据库操作类
 */
public class RoleDao {

    /**
     * 查询所有角色信息
     * @return List<RoleEntity> 角色信息的集合
     */
    public List<RoleEntity> selectRole(){
        List<RoleEntity> roleList =new ArrayList<>();
        Connection con= null;
        PreparedStatement pst = null;
        ResultSet rs=null;
        try {
            con = JdbcUtils.getConnection();
            //查询所有角色
            String sql="select * from role";
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                RoleEntity roleEntity=new RoleEntity();
                roleEntity.setRoleId(rs.getInt("roleId"));
                roleEntity.setRoleName(rs.getString("roleName"));
                roleList.add(roleEntity);//添加到集合
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(rs,pst,con);
        }
        return  roleList;
    }
    /**
     * 查询所有父功能
     * @return List<RoleTreeEntity> 获取所有父功能树的实体对象集合
     */
    public List<RoleTreeEntity> selectRoleRight(int roleId){
        List<RoleTreeEntity> roleTreeList =new ArrayList<>();
        Connection con= null;
        PreparedStatement pst = null;
        ResultSet rs=null;
        try {
            con = JdbcUtils.getConnection();

            //查询权限编号、权限名、权限-角色流水编号，并保存到数集合当中
            String sql="  SELECT f.funId,f.funName,IFNULL(rr.rightId,0) as rightId" +
                    "  FROM (SELECT * FROM fun WHERE funType='Folder') f " +
                    "  LEFT JOIN ( SELECT * FROM role_right WHERE roleId=?)rr ON f.funId=rr.funId";
            pst = con.prepareStatement(sql);
            pst.setInt(1,roleId);
            rs = pst.executeQuery();
            while (rs.next()) {
                RoleTreeEntity roleTreeEntity=new RoleTreeEntity();
                String fundId=rs.getString("funId");
                roleTreeEntity.setId(fundId);
                roleTreeEntity.setTitle(rs.getString("funName"));
                //传参功能编号和角色编号查询对应的子树，返回树集合
                List<RoleTreeEntity> roleRightChildrenList = selectRoleRightChildren(fundId,roleId);
                //判断子树集合中是否有数据
                if (roleRightChildrenList.size()!=0){
                    //取得权限-角色流水编号
                    int rightId = rs.getInt("rightId");
                    //判断编号是否为0,0:false 非0:true 修改到状态变量中
                    roleTreeEntity.setChecked(rightId==0?false:true);
                    //修改子树集合
                    roleTreeEntity.setChildren(roleRightChildrenList);
                    //添加到集合
                    roleTreeList.add(roleTreeEntity);
                }else{//判断如果没有子树
                    //取得权限-角色流水编号
                    int rightId = rs.getInt("rightId");
                    //判断编号是否为0,0:false 非0:true 修改到状态变量中
                    roleTreeEntity.setChecked(rightId==0?false:true);
                    //添加到集合
                    roleTreeList.add(roleTreeEntity);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(rs,pst,con);
        }
        return  roleTreeList;
    }
    /**
     * 根据父功能ID 查询该父功能所有子功能集合
     * @return List<RoleTreeEntity> 获取该父功能下所有子功能树的实体对象集合
     */
    public List<RoleTreeEntity> selectRoleRightChildren(String funParentId,int roleId){
        List<RoleTreeEntity> roleList =new ArrayList<>();
        Connection con= null;
        PreparedStatement pst = null;
        ResultSet rs=null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        String sql="SELECT f.funId,f.funName,IFNULL(rr.rightId,0) as rightId" +
                "  FROM (SELECT * FROM fun WHERE funType='document' AND funParentId=?) f " +
                "  LEFT JOIN ( SELECT * FROM role_right where roleId=? )rr ON f.funId=rr.funId" +
                "  ";
            pst = con.prepareStatement(sql);
//            pst.setInt(1,funParentId);
            pst.setString(1,funParentId);
            pst.setInt(2,roleId);
            rs = pst.executeQuery();
            while (rs.next())
            {
                RoleTreeEntity roleTreeEntity=new RoleTreeEntity();
//                roleTreeEntity.setId(rs.getInt("funId"));
                roleTreeEntity.setId(rs.getString("funId"));
                roleTreeEntity.setTitle(rs.getString("funName"));
                int rightId = rs.getInt("rightId");
                roleTreeEntity.setChecked(rightId==0?false:true);
                roleList.add(roleTreeEntity);//添加到集合
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(rs,pst,con);
        }
        return  roleList;
    }

    /**
     * 增加权限信息
     * @param funId 功能ID
     * @param roleId 角色ID
     * @return boolean类型 增加成功 则返回true 否则返回false
     */
    public boolean  insertRoleRight(int funId, int roleId){
        boolean flag=false;
        Connection con= null;
        PreparedStatement pst = null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        String sql="INSERT INTO role_right(funId,roleId) VALUES(?,?);";
            pst = con.prepareStatement(sql);
            pst.setInt(1,funId);
            pst.setInt(2,roleId);
            int result=pst.executeUpdate();
            flag = result>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(null,pst,con);
        }

        return flag;
    }
    /**
     * 根据角色ID删除所有权限功能
     * @param roleId 角色ID
     * @return boolean类型 增加成功 则返回true 否则返回false
     */
    public boolean deleteRoleRight(int roleId){
        boolean flag=false;
        Connection con= null;
        PreparedStatement pst = null;
        try {
            con = JdbcUtils.getConnection();
        //第二步 语句对象
        String sql="DELETE FROM role_right WHERE roleId=?";
            pst = con.prepareStatement(sql);
            pst.setInt(1,roleId);
            int result=pst.executeUpdate();
            flag = result>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.close(null,pst,con);
        }

        return flag;
    }
    public static void main(String[] args) {
        UserEntity userEntity=new UserEntity(6,"chenjia","777777",1);
     /*  boolean flag= new UserDao().updateUser(userEntity);
        System.out.println("flag=" + flag);*/
      //  new UserDao().insertUser(userEntity);
      //  boolean flag=new UserDao().deleteUser(4);
        //System.out.println(flag);
        List<RoleEntity> roleList = new RoleDao().selectRole();
        for(RoleEntity role:roleList){
            //System.out.println(role);
        }

    }

    /**
     * 修改角色对应权限
     * @param roleId
     * @param parmList
     */
    public void updateRoleRight(int roleId,String parmList){
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            conn= JdbcUtils.getConnection();
        String sql="INSERT INTO role_right(roleId,funId) VALUES(?,?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,roleId);
            pstmt.setString(2,parmList);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            new RuntimeException(e.getMessage(),e);
        }finally{
            JdbcUtils.close(null,pstmt,conn);
        }

    }

    /**
     * 删除角色对应所有权限
     * @param roleId
     */
    public void deleteRoleRights(int roleId){
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            conn= JdbcUtils.getConnection();
        String sql="DELETE FROM role_right WHERE roleId=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,roleId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            new RuntimeException(e.getMessage(),e);
        }finally{
            JdbcUtils.close(null,pstmt,conn);
        }
    }

    /**
     * 添加角色
     * @param roleName
     * @return
     */
    public boolean addRole(String roleName){
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            conn=JdbcUtils.getConnection();
            String sql="insert into role(roleName) values(?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,roleName);
            int row=pstmt.executeUpdate();
            if (row!=0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.close(null,pstmt,conn);
        }
        return false;
    }

}
